package com.wifi.user.dao;

import com.wifi.unti.DBHelper;
import com.wifi.user.entity.Menu;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class MenuDaoImp implements MenuDao {
    @Override
    public List<Object> selectMenuByUid(int id) throws SQLException {
      String  sql="SELECT m.* from tb_menu m ,tb_ulinkm l WHERE m.id=l.mid and l.uid=? and m.status=0";
        return DBHelper.selectAll(sql,new Menu(),id);
    }

    @Override
    public long selectCount(String pid) throws SQLException {
        QueryRunner runner= new QueryRunner(DBHelper.getDataSource());
        StringBuffer stringBuffer=new StringBuffer("SELECT COUNT(*) FROM `shenzhou`.`tb_menu`");
        if (!pid.isEmpty()){
            stringBuffer.append("where pid=");
            stringBuffer.append(pid);
        }
        String sql=stringBuffer.toString();
        System.out.println(sql);
        long count = runner.query(sql,new ScalarHandler<Long>());
        return count;
    }

    @Override
    public List<Object> selectMenu(Integer pageNum, Integer pageSize,String pid) throws SQLException {
        StringBuffer stringBuffer=new StringBuffer("SELECT m.*,n.name fatherName FROM `shenzhou`.`tb_menu` m left join `shenzhou`.`tb_menu` n on m.pid=n.id ");
        if (!pid.isEmpty()){
            stringBuffer.append("where m.pid=");
            stringBuffer.append(pid);
        }
        stringBuffer.append(" LIMIT ?,?");
        String sql=stringBuffer.toString();
        System.out.println(sql);
//        String sql="SELECT m.*,n.name fatherName FROM `shenzhou`.`tb_menu` m left join `shenzhou`.`tb_menu` n on m.pid=n.id LIMIT ?,?";
        return DBHelper.selectAll(sql,new Menu(),pageNum,pageSize);
    }
@Override
    public  List<Object> selectFatherMenu() throws SQLException {
            String sql="SELECT * FROM `shenzhou`.`tb_menu`  where pid=0;";
            return DBHelper.selectAll(sql,new Menu());
    }

    @Override
    public Integer changeStatus(Integer id, Integer status) throws SQLException {
        String sql;
        if ( status==0){
            sql="UPDATE `shenzhou`.`tb_menu` SET `status` = 1 WHERE `id` = ?";
        }else if ( status==1){
            sql="UPDATE `shenzhou`.`tb_menu` SET `status`= 0 WHERE `id` = ?";
        }else{
            return 0;
        }
        return DBHelper.update(sql,id);
    }

    @Override
    public Long insertMenu(Menu menu) throws SQLException {
        String sql="INSERT INTO `shenzhou`.`tb_menu`(`name`, `pid`, `link`, `icon`, `status`) VALUES (?, ?, ?, ?, ?)";
        return DBHelper.insert(sql,menu.getName(),menu.getPid(),menu.getLink(),menu.getIcon(),menu.getStatus());
    }

    @Override
    public Object selectMenu(String name,Integer role) throws SQLException {
        String sql="SELECT * FROM `shenzhou`.`tb_menu` where name=? and pid=?";
        return DBHelper.select(sql,new Menu(),name,role);
    }

    @Override
    public Object selectMenuByMid(Integer id) throws SQLException {
        String sql="SELECT * FROM `shenzhou`.`tb_menu` where id=?";
        return DBHelper.select(sql,new Menu(),id);
    }

    @Override
    public Integer updateMenu(Menu menu) throws SQLException {
        String sql="UPDATE `shenzhou`.`tb_menu` SET `name` = ?, `pid` = ?, `link` = ?, `icon` = ?, `status` = ? WHERE `id` =?";
        return DBHelper.update(sql,menu.getName(),menu.getPid(),menu.getLink(),menu.getIcon(),menu.getStatus(),menu.getId());
    }

    @Override
    public long insertUserMenu(Integer uid, Integer mid) throws SQLException {
        String sql="INSERT INTO `shenzhou`.`tb_ulinkm`(`uid`, `mid`) VALUES (?,?)";
        return DBHelper.insert(sql,uid,mid);
    }

    @Override
    public Integer DeleteEmptyByid(Integer valueOf) throws SQLException {
        String sql="DELETE FROM `shenzhou`.`tb_ulinkm` WHERE `uid` = ?";
        return DBHelper.update(sql,valueOf);
    }
}
